const express = require('express');
const db = require('../db');
const md5=require('../util/md5')
//导入判断目录是否已经存在的处理模块
const mkdir = require('../util/mkdirs')
//引入中间件
const multer = require('multer');
var fs = require('fs');
  const path = require('path'); // 导入 path 模块
const router = express.Router();
//使用multer解析FormData数据
const storage = multer.memoryStorage(); // 将文件存储在内存中
const upload = multer({ storage: storage });
//登录验证jwt包
const jwt = require("jsonwebtoken");
//简易随机生成
const secretKey = 'weiyinglin';
const moment = require('moment');
const schedule = require('node-schedule');


//获取机构信息接口
router.get('/getOrganizationInfo',(req,res)=>{
    const selectQuery = 'SELECT * FROM  organizationInfo ';
    db.query(selectQuery,[],(error,rows)=>{
        if (error) {
            console.error('Error executing query:', error);
            return res.status(500).json({ error: 'Error executing query' });
          }
          return res.status(201).json(rows)
    })
})
//根据机构查询对应咨询师信息接口
router.get('/getConsultantsByOrdid',(req,res)=>{
  const {org_id}=req.query
  // console.log(org_id)
  const selectQuery='SELECT consultants.* FROM consultants INNER JOIN consultant_organizations ON  consultants.id = consultant_organizations.consultant_id WHERE consultant_organizations.organization_id = ?'
  db.query(selectQuery,[org_id],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    return res.status(201).json(rows)
  })
})

//获取城市信息接口
router.get('/getCities',(req,res)=>{
  const selectQuery='SELECT * FROM cities'
  db.query(selectQuery,(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
      return res.status(201).json(rows)
  })
})

//根据不同城市搜索对应咨询师信息接口
router.get('/getConsultantsByCityid',(req,res)=>{
  const {city_id}=req.query
  const selectQuery='SELECT consultants.* FROM consultants  INNER JOIN consultant_cities  ON consultants.id = consultant_cities.consultant_id WHERE consultant_cities.city_id = ?'
  db.query(selectQuery,[city_id],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    return res.status(201).json(rows)
  })
  
})

//获取医生擅长领域信息接口
router.get('/getSpecialties',(req,res)=>{
  const selectQuery='SELECT * FROM specialties'  
  db.query(selectQuery,(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    return  res.status(201).json(rows)
  })
})

//根据不同擅长领域搜索对应咨询师信息
router.get('/getConsultantsBySpeciality',(req,res)=>{
  const {spe_id}=req.query
  const selectQuery='SELECT consultants.* FROM consultants INNER JOIN consultant_specialties ON consultants.id=consultant_specialties.consultant_id WHERE consultant_specialties.specialty_id=?  '
 db.query(selectQuery,[spe_id],(err,rows)=>{
  if (err) {
    console.error('Error inserting user to database', err);
    return res.status(500).json({ message: '服务器错误' });
  } 
  return res.status(201).json(rows)
 })
})

//获取咨询师详情信息
router.get('/getDetailConsultantInfo',(req,res)=>{
  const {id}=req.query
  const selectQuery='SELECT * FROM consultants   WHERE id=?'
  db.query(selectQuery,[id],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    return res.status(201).json(rows[0])
  })
  
})

//获取咨询师空闲时间信息接口
router.get('/getavailableTime',(req,res)=>{
    const {id}=req.query
    const selectTimeQuery='SELECT * FROM consultant_schedule WHERE coun_id=? AND start_time BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);'
    db.query(selectTimeQuery,[id],(err,rows)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      rows.forEach((row)=>{
        const {start_time,end_time}=row
       // 将 UTC 时间转换为本地时间
          row.start_time=start_time.toLocaleString();
          row.end_time=end_time.toLocaleString()
      })
      return res.status(201).json(rows)
   })
})

//根据日期查询咨询师空闲信息接口
router.get('/getAvailableByTime',(req,res)=>{
     const{id,time}=req.query;
    //  console.log(id,time)
     const  selectQuery='SELECT * FROM consultant_schedule WHERE coun_id=? AND DATE(start_time)=?AND start_time>NOW()'
     db.query(selectQuery,[id,time],(err,rows)=>{
       if (err) {
         console.error('Error inserting user to database', err);
         return res.status(500).json({ message: '服务器错误' });
       } 
       rows.forEach((row)=>{
         const {start_time,end_time}=row
        // 将 UTC 时间转换为本地时间
           row.start_time=start_time.toLocaleString();
           row.end_time=end_time.toLocaleString()
       })
       res.status(201).json(rows)
      })
})

//查询今日咨询师默认空闲信息
router.get('/getAvailableToday',(req,res)=>{
  const {id,today}=req.query;
  // console.log(id,today)
  const  selectQuery='SELECT * FROM consultant_schedule WHERE coun_id=? AND DATE(start_time)=? AND start_time>NOW()'
  db.query(selectQuery,[id,today],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    rows.forEach((row)=>{
      const {start_time,end_time}=row
     // 将 UTC 时间转换为本地时间
        row.start_time=start_time.toLocaleString();
        row.end_time=end_time.toLocaleString()
    })
    res.status(201).json(rows)
  })
 

})

//预约时间接口
router.post('/appointment',upload.none(),(req,res)=>{
  const {account,counselor_id,date,start_time,end_time,selectedTimeSlotId}=req.body;
  // console.log(account,counselor_id,date,start_time,end_time)
  const selectQuery1='SELECT id FROM users WHERE account=?'
  db.query(selectQuery1,[account],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    if(rows.length>0){
      const id=rows[0].id
      const status='Scheduled'
      const insertQuery = 'INSERT INTO appointments (user_id, counselor_id, date, start_time, end_time,status) VALUES (?, ?, ?, ?,?, ?)';
      db.query(insertQuery, [id, counselor_id, date, start_time, end_time,status], (err, result) => {
        if (err) {
          console.error('Error inserting appointment to database', err);
          return res.status(500).json({ message: '服务器错误' });
        }
       const updateQuery='UPDATE consultant_schedule SET is_booked =1 WHERE id=? '
       db.query(updateQuery,[selectedTimeSlotId],(err,rows)=>{
        if (err) {
          console.error('Error inserting appointment to database', err);
          return res.status(500).json({ message: '服务器错误' });
        }
        else{
          if(rows.affectedRows>0){
            return res.status(201).json({ message: '预约成功' });
          }
          else{
            // console.log('我在这')
            return res.status(402).json({error:'预约失败'})
          }
        }
       
       })
      });
    }
    else{
      return res.status(404).json({ error: '用户不存在' });
    }
  })
})

//获取用户全部预约信息接口
router.get('/getAppointmentData',(req,res)=>{
    const {user_id}=req.query
    // console.log(user_id)
    const selectQuery="SELECT appointments.*, consultants.* FROM appointments INNER JOIN consultants ON appointments.counselor_id=consultants.id WHERE appointments.user_id=? ORDER BY STR_TO_DATE(appointments.created_at, '%Y-%m-%d %H:%i:%s') DESC"
    db.query(selectQuery,[user_id],(err,rows)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      // console.log(rows)
      rows.forEach((row)=>{
          if(row.status=='Scheduled'){
            const currentDate=new Date()
            const targetDate=new Date(row.date)
            const updateQuery="UPDATE appointments SET status='Completed' WHERE appointment_id=?"
            if(currentDate>targetDate){
               db.query(updateQuery,[row.appointment_id],(err,rows)=>{
                 if (err) {
                   console.error('Error inserting user to database', err);
                   return res.status(500).json({ message: '服务器错误' });
                 } 
               })
               row.status='Completed'
            } 
          }
              // console.log(row)
      })
      return res.status(201).json(rows)
    })
})

//获取用户预约状态为scheduled的信息接口
router.get('/getScheduledData',(req,res)=>{
    const {user_id}=req.query
    const selectQuery="SELECT appointments.*, consultants.* FROM appointments INNER JOIN consultants ON appointments.counselor_id=consultants.id WHERE appointments.user_id=? AND appointments.status='Scheduled' ORDER BY STR_TO_DATE(appointments.created_at, '%Y-%m-%d %H:%i:%s') DESC"
    db.query(selectQuery,[user_id],(err,rows)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      
      return res.status(201).json(rows)
    })
})

//获取用户预约状态为scheduled并且时间在今天以后得信息接口
router.get('/getScheduledDataAfter',(req,res)=>{
  const {user_id}=req.query
  const selectQuery="SELECT appointments.*, consultants.* FROM appointments INNER JOIN consultants ON appointments.counselor_id=consultants.id WHERE appointments.user_id=? AND appointments.status='Scheduled' AND appointments.date>=CURDATE() ORDER BY STR_TO_DATE(appointments.created_at, '%Y-%m-%d %H:%i:%s') DESC"
  db.query(selectQuery,[user_id],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    
    return res.status(201).json(rows)
  })
})

//将用户预约状态为scheduled修改为completed的接口
router.post('/updateScheduledIntoCompletd',(req,res)=>{
  const {appointment_id}=req.body
  const updateQuery="UPDATE appointments SET status='Completed' WHERE appointment_id=?"
  db.query(updateQuery,[appointment_id],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    if(rows.affectedRows>0){
      res.status(201).json({message:'修改成功'})
    }
    else{
      return res.status(402).json({error:'修改失败'})
    }
  })

})
//获取用户预约状态为取消的信息接口
router.get('/getCancelledData',(req,res)=>{
  const {user_id}=req.query
  const selectQuery="SELECT appointments.*, consultants.* FROM appointments INNER JOIN consultants ON appointments.counselor_id=consultants.id WHERE appointments.user_id=? AND appointments.status='Cancelled' ORDER BY STR_TO_DATE(appointments.created_at, '%Y-%m-%d %H:%i:%s') DESC"
    db.query(selectQuery,[user_id],(err,rows)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      
      return res.status(201).json(rows)
    })
})

//获取用户预约状态为已完成的信息接口
router.get('/getCompletedData',(req,res)=>{
  const{user_id}=req.query
  const selectQuery="SELECT appointments.*, consultants.* FROM appointments INNER JOIN consultants ON appointments.counselor_id=consultants.id WHERE appointments.user_id=? AND appointments.status='Completed' ORDER BY STR_TO_DATE(appointments.created_at, '%Y-%m-%d %H:%i:%s') DESC"
  db.query(selectQuery,[user_id],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    
    return res.status(201).json(rows)
  })
})

//取消用户预约信息接口
router.post('/cancelBooking',upload.none(),(req,res)=>{
  const {coun_id,appointment_id,date,start_time,end_time}=req.body
  // console.log(coun_id,appointment_id,date,start_time,end_time)
  function formatDateTime(dateString, timeString) {
    const dateTimeString = `${dateString} ${timeString}`;
    const formattedDateTime = moment(dateTimeString, 'YYYY-MM-DD HH:mm').format('YYYY-MM-DD HH:mm:ss');
  
    return formattedDateTime;
  }
  const start_timeData =formatDateTime(date,start_time)
  const end_timeData=formatDateTime(date,end_time)
  // console.log(start_timeData,end_timeData)
  const updateQuery="UPDATE appointments SET status='Cancelled' WHERE appointment_id=?"
  db.query(updateQuery,[appointment_id],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    const updateQuery1='UPDATE consultant_schedule SET is_booked=NULL  WHERE coun_id=? AND start_time=? AND end_time=?'
    db.query(updateQuery1,[coun_id,start_timeData,end_timeData],(err,rows)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      else{
        // console.log(rows)
        if(rows.affectedRows>0){
          // console.log(rows)
          return res.status(201).json({message:'取消预约成功'})
        }
        else{
          return res.status(402).json({error:'取消预约失败'})
        }
      }
  
    })
  })
})



module.exports=router